package com.lanou3g.ptp.utils;


import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelUtils {
    // 解析工具
    public static String getCellValue(Cell cell) {
        Object result = "";
        if (cell != null) {
            cell.setCellType(CellType.STRING);
            result = cell.getStringCellValue();
        }
        return result.toString();
    }

    /**
     * 给目标Sheet添加下拉框，即数据 验证
     *
     * @param firstRow 作用区域的开始行数索引 0 开始
     * @param lastRow  作用区域的结束行数索引 0 开始
     * @param firstCol 作用区域的开始列数索引 0 开始
     * @param lastCol  作用区域的结束列数索引 0 开始
     * @param sheet    作用的sheet对象
     * @param strs     下拉框的内容
     * @Description
     * @author PMC
     * @date 2018年8月12日
     */
    public static Sheet setValidationData(int firstRow, int lastRow,int firstCol, int lastCol,
                                          Sheet sheet, String[] strs) throws IllegalArgumentException {
        if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0 || lastRow < firstRow || lastCol < firstCol) {
            throw new RuntimeException("索引值不合规范 : " + firstRow + ":" + lastRow + ":" + firstCol + ":" + lastCol);
        }
        if (sheet instanceof XSSFSheet) {
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(strs);
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
            validation.setSuppressDropDownArrow(true);//是否显示下拉框箭头
            validation.setShowErrorBox(true);//是否提示错误
            sheet.addValidationData(validation);
        } else if (sheet instanceof HSSFSheet) {
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strs);
            DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }
        return sheet;
    }

    /**
     * 依据内容判断是否为excel2003及以下
     */

    public static boolean isExcel2003(String filePath) {
        try {
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(filePath));
            if (POIFSFileSystem.hasPOIFSHeader(bis)) {
                System.out.println("Excel版本为excel2003及以下");
                return true;
            }
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return false;
    }

}
